USE SAS
GO

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'GetMyCities')
	BEGIN
		DROP  Procedure  dbo.GetMyCities
	END

GO

CREATE Procedure dbo.GetMyCities
	@USERID		INT
,	@ISACTIVE	VARCHAR(10) = NULL
AS
BEGIN
	
	
	SET @ISACTIVE = CASE
						WHEN @ISACTIVE IS NULL THEN NULL
						WHEN @ISACTIVE IN ('X','1') THEN '1' 
						ELSE '0' 
					END

	SELECT 
		c.CityNo					AS [City No]
	,	c.CityName					AS [City]
	,	CASE ISNULL(c.IsActive,0) WHEN 1 THEN 'Yes' ELSE 'No' END as [Active]
	,	c.CityNo					AS [PKID]
	FROM TR_CITY as c
	INNER JOIN TR_PARTNER as p
		ON p.PartnerGUID = c.UserGUID
	WHERE 
		p.PartnerNo = @userID
	AND ISNULL(c.IsActive,0) = ISNULL(@ISACTIVE,c.ISACTIVE)
	ORDER BY c.IsActive DESC, CityName ASC

END
GO

-- exec GetMyCities 3